
--     中北在线商场购物系统  数据库创建脚本

--  1.删除旧的数据库对象，如果是第一次建库，请不要执行这段代码

drop trigger tib_goods;
drop trigger tib_roleright;
drop trigger tib_shoppingrecord;
drop trigger tib_sysfunction;
drop trigger tib_sysrole;
drop trigger tib_sysuser;

alter table ROLERIGHT drop constraint FK_ROLERIGH_FK_FUN_RR_SYSFUNCT;
alter table ROLERIGHT drop constraint FK_ROLERIGH_FK_ROLE_R_SYSROLE;
alter table SHOPPINGRECORD drop constraint FK_SHOPPING_FK_GOODS__GOODS;
alter table SHOPPINGRECORD drop constraint FK_SHOPPING_FK_USER_R_SYSUSER;
alter table SYSUSER drop constraint FK_SYSUSER_FK_ROLE_U_SYSROLE;

drop table GOODS cascade constraints;
drop table ROLERIGHT cascade constraints;
drop table SHOPPINGRECORD cascade constraints;
drop table SYSFUNCTION cascade constraints;
drop table SYSROLE cascade constraints;

drop index FK_ROLE_RR_FK;
drop index FK_FUN_RR_FK;
drop index FK_GOODS_RECORD_FK;
drop index FK_USER_RECORD_FK;
drop index FK_ROLE_USER_FK;

drop sequence S_GOODS;
drop sequence S_ROLERIGHT;
drop sequence S_SHOPPINGRECORD;
drop sequence S_SYSFUNCTION;
drop sequence S_SYSROLE;
drop sequence S_SYSUSER;

--  2.创建新的数据库对象

--  2.1 创建用于自增长的序列

create sequence S_GOODS;
create sequence S_ROLERIGHT;
create sequence S_SHOPPINGRECORD;
create sequence S_SYSFUNCTION;
create sequence S_SYSROLE;
create sequence S_SYSUSER;

-- 2.2 创建数据表并添加索引和约束
create table GOODS (
   GID                  NUMBER(6)            not null,
   GNAME                VARCHAR2(60)         not null,
   GCOUNT               INTEGER,
   GPRICE               NUMBER,
   GDESC                VARCHAR2(2000),
   constraint PK_GOODS primary key (GID)
);

create table ROLERIGHT (
   RRID                 NUMBER(6)            not null,
   FUNID                NUMBER(6),
   ROLEID               NUMBER(6),
   constraint PK_ROLERIGHT primary key (RRID)
);

create table SHOPPINGRECORD (
   SRID                 NUMBER(6)            not null,
   UID                INTEGER,
   GOODSID              INTEGER,
   COUNT                INTEGER,
   constraint PK_SHOPPINGRECORD primary key (SRID)
);

create table SYSFUNCTION (
   FUNID                NUMBER(6)            not null,
   FUNNAME              VARCHAR2(20),
   FUNURL               VARCHAR2(200),
   FUNPID               INTEGER,
   FUNSTATE             INTEGER,
   constraint PK_SYSFUNCTION primary key (FUNID)
);

create table SYSROLE (
   ROLEID               NUMBER(6)            not null,
   ROLENAME             VARCHAR2(20),
   ROLESTATE            INTEGER,
   ROLEDESC             VARCHAR2(200),
   constraint PK_SYSROLE primary key (ROLEID)
);

create table SYSUSER (
   USERID               NUMBER(6)            not null,
   ROLEID               NUMBER(6),
   USERNAME             VARCHAR2(20),
   USERPWD              CHAR(20),
   USERTRUENAME         VARCHAR2(30),
   USERACCOUNT          NUMBER,
   USERSTATE            INTEGER,
   constraint PK_SYSUSER primary key (USERID)
);

create index FK_FUN_RR_FK on ROLERIGHT (
   FUNID ASC
);

create index FK_ROLE_RR_FK on ROLERIGHT (
   ROLEID ASC
);

create index FK_USER_RECORD_FK on SHOPPINGRECORD (
   UID ASC
);

create index FK_GOODS_RECORD_FK on SHOPPINGRECORD (
   GOODSID ASC
);

create index FK_ROLE_USER_FK on SYSUSER (
   ROLEID ASC
);

alter table ROLERIGHT add constraint FK_ROLERIGH_FK_FUN_RR_SYSFUNCT 
	foreign key (FUNID) references SYSFUNCTION (FUNID);

alter table ROLERIGHT add constraint FK_ROLERIGH_FK_ROLE_R_SYSROLE 
	foreign key (ROLEID) references SYSROLE (ROLEID);
   
alter table SHOPPINGRECORD add constraint FK_SHOPPING_FK_GOODS__GOODS 
	foreign key (GOODSID) references GOODS (GID);

alter table SHOPPINGRECORD add constraint FK_SHOPPING_FK_USER_R_SYSUSER 
	foreign key (UID) references SYSUSER (USERID);

alter table SYSUSER add constraint FK_SYSUSER_FK_ROLE_U_SYSROLE 
	foreign key (ROLEID) references SYSROLE (ROLEID);

-- 2.3 创建用于自增长的触发器

create trigger tib_goods 
before insert on GOODS 
for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
begin
    select S_GOODS.NEXTVAL INTO :new.GID from dual;
exception
    when integrity_error then raise_application_error(errno, errmsg);
end;
/

create trigger tib_roleright 
before insert on ROLERIGHT 
for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
begin
    select S_ROLERIGHT.NEXTVAL INTO :new.RRID from dual;
exception
    when integrity_error then raise_application_error(errno, errmsg);
end;
/

create trigger tib_shoppingrecord 
before insert on SHOPPINGRECORD 
for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
begin
    select S_SHOPPINGRECORD.NEXTVAL INTO :new.SRID from dual;
exception
    when integrity_error then raise_application_error(errno, errmsg);
end;
/

create trigger tib_sysfunction 
before insert on SYSFUNCTION 
for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
begin
    select S_SYSFUNCTION.NEXTVAL INTO :new.FUNID from dual;
exception
    when integrity_error then raise_application_error(errno, errmsg);
end;
/

create trigger tib_sysrole 
before insert on SYSROLE 
for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
begin
    select S_SYSROLE.NEXTVAL INTO :new.ROLEID from dual;
exception
    when integrity_error then raise_application_error(errno, errmsg);
end;
/

create trigger tib_sysuser 
before insert on SYSUSER 
for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
begin
    select S_SYSUSER.NEXTVAL INTO :new.USERID from dual;
exception
    when integrity_error then raise_application_error(errno, errmsg);
end;
/
